/**
 * 
 */
package dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import model.CTDTM;
import model.NganhM;
import util.DAL;
import util.MyConnectUnit;


public class CTDTD {
	private MyConnectUnit conn;
	
	/**
	 * @function constructor CTDTD
	 * @throws Exception 
	 */
	public CTDTD() throws Exception {
		super();
		this.conn = DAL.getDAL();
	}

	/**
	 * @function get all CTDT
	 * @param from
	 * @param to
	 * @return
	 * @throws Exception
	 */
	public ArrayList<CTDTM> getAlls(String ten, int from, int to) throws Exception {
		String limit = from + ", " + to;
		ArrayList<CTDTM> ls = new ArrayList<CTDTM>();
		ResultSet rs = this.conn.Selects(
						"id_ctdt, ten_van_tat, ten_day_du, ten_bang, so_qd, ngay_qd, c.id_nganh, n.ten, so_nam_tk, so_nam_td",
						"CTDT c, NGANH n",
						"c.id_nganh = n.id_nganh AND ten_van_tat LIKE '%" + ten + "%'",
						"ten_van_tat ASC",
						limit);
		CTDTM l = null;
		while (rs.next()) {
			l = new CTDTM();
			l.setId_ctdt(rs.getInt("id_ctdt"));
			l.setTen_van_tat(rs.getString("ten_van_tat"));
			l.setTen_day_du(rs.getString("ten_day_du"));
			l.setTen_bang(rs.getString("ten_bang"));
			l.setSo_qd(rs.getString("so_qd"));
			l.setNgay_qd(rs.getString("ngay_qd"));
			
			l.setId_nganh(rs.getInt("c.id_nganh"));
			NganhM n = new NganhM();
			n.setTen(rs.getString("n.ten"));
			l.setTen_nganh(n);
			
			l.setSo_nam_tk(rs.getInt("so_nam_tk"));
			l.setSo_nam_td(rs.getInt("so_nam_td"));

			ls.add(l);
		}
		return ls;
	}

	/**
	 * @function get all CTDT
	 * @param Condition
	 * @param OrderBy
	 * @return
	 * @throws Exception
	 */
	public ArrayList<CTDTM> getAll(String Condition, String OrderBy) throws Exception {
		ArrayList<CTDTM> ls = new ArrayList<CTDTM>();
		ResultSet rs = this.conn.Select("CTDT", Condition, OrderBy);
		CTDTM l = null;
		while (rs.next()) {
			l = new CTDTM();
			l.setId_ctdt(rs.getInt("id_ctdt"));
			l.setTen_van_tat(rs.getString("ten_van_tat"));
			l.setTen_day_du(rs.getString("ten_day_du"));
			ls.add(l);
		}
		return ls;
	}

	/**
	 * @function get all CTDT with Condition
	 * @param Condition
	 * @return
	 * @throws Exception
	 */
	public ArrayList<CTDTM> getAll(String Condition) throws Exception {
		return getAll(Condition, null);
	}

	/**
	 * @function get all CTDT no Condition
	 * @return
	 * @throws Exception
	 */
	public ArrayList<CTDTM> getAll() throws Exception {
		return getAll(null);
	}

	/**
	 * @function search ten van tat CTDT
	 * @param ten
	 * @return
	 * @throws Exception
	 */
	public ArrayList<CTDTM> searchCTDT(String ten) throws Exception {
		ArrayList<CTDTM> list = getAll("ten_van_tat LIKE '%" + ten + "%'");
		return list;
	}
	
	/**
	 * @function get all CTDT
	 * @param from
	 * @param to
	 * @return
	 * @throws Exception
	 */
	public ArrayList<CTDTM> loadTheoNganh(int id_nganh, int from, int to) throws Exception {
		
		ArrayList<CTDTM> ls = new ArrayList<CTDTM>();
		String sql = "SELECT id_ctdt, ten_van_tat, ten_day_du, ten_bang, so_qd, ngay_qd, ctdt.id_nganh, n.ten, so_nam_tk, so_nam_td " +
				"FROM CTDT ctdt, NGANH n " +
				"WHERE ctdt.id_nganh = n.id_nganh AND ctdt.id_nganh = " + id_nganh +
				" LIMIT " + from + ", " + to;
		ResultSet rs = this.conn.SelectOption(sql);
		CTDTM l = null;
		while (rs.next()) {
			l = new CTDTM();
			l.setId_ctdt(rs.getInt("id_ctdt"));
			l.setTen_van_tat(rs.getString("ten_van_tat"));
			l.setTen_day_du(rs.getString("ten_day_du"));
			l.setTen_bang(rs.getString("ten_bang"));
			l.setSo_qd(rs.getString("so_qd"));
			l.setNgay_qd(rs.getString("ngay_qd"));
			
			l.setId_nganh(rs.getInt("ctdt.id_nganh"));
			NganhM n = new NganhM();
			n.setTen(rs.getString("n.ten"));
			l.setTen_nganh(n);
			
			l.setSo_nam_tk(rs.getInt("so_nam_tk"));
			l.setSo_nam_td(rs.getInt("so_nam_td"));

			ls.add(l);
		}
		return ls;
	}
	
public int countTheoNganh(int id_nganh) throws Exception {
		String sql = "SELECT COUNT(*) AS rows " +
				"FROM CTDT " +
				"WHERE id_nganh = " + id_nganh ;
		int  n =0;
		ResultSet rs = this.conn.SelectOption(sql);
		if(rs.next()){
			n=rs.getInt("rows");
		}
		return n;
}

/**
 * @function get all CTDT
 * @param from
 * @param to
 * @return
 * @throws Exception
 */
public ArrayList<CTDTM> loadTheoDonVi(int id_don_vi, int from, int to) throws Exception {
	
	ArrayList<CTDTM> ls = new ArrayList<CTDTM>();
	String sql = "SELECT id_ctdt, ten_van_tat, ten_day_du, ten_bang, so_qd, ngay_qd, ctdt.id_nganh, n.ten, so_nam_tk, so_nam_td " +
			"FROM CTDT ctdt, NGANH n " +
			"WHERE ctdt.id_nganh = n.id_nganh AND n.id_don_vi_ql = " + id_don_vi +
			" LIMIT " + from + ", " + to;
	ResultSet rs = this.conn.SelectOption(sql);
	CTDTM l = null;
	while (rs.next()) {
		l = new CTDTM();
		l.setId_ctdt(rs.getInt("id_ctdt"));
		l.setTen_van_tat(rs.getString("ten_van_tat"));
		l.setTen_day_du(rs.getString("ten_day_du"));
		l.setTen_bang(rs.getString("ten_bang"));
		l.setSo_qd(rs.getString("so_qd"));
		l.setNgay_qd(rs.getString("ngay_qd"));
		
		l.setId_nganh(rs.getInt("ctdt.id_nganh"));
		NganhM n = new NganhM();
		n.setTen(rs.getString("n.ten"));
		l.setTen_nganh(n);
		
		l.setSo_nam_tk(rs.getInt("so_nam_tk"));
		l.setSo_nam_td(rs.getInt("so_nam_td"));

		ls.add(l);
	}
	return ls;
}

/*public int countTheoDonVi(int id_dv) throws Exception {
	String sql = "SELECT COUNT(*) AS rows " +
			"FROM CTDT ctdt " +
			"WHERE id_nganh = " + id_nganh ;
	int  n =0;
	ResultSet rs = this.conn.SelectOption(sql);
	if(rs.next()){
		n=rs.getInt("rows");
	}
	return n;
}*/

	/**
	 * @function insert CTDT
	 * @param ctdt
	 * @return
	 * @throws Exception
	 */
	public boolean Insert(CTDTM ctdt) throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("ten_van_tat", ctdt.getTen_van_tat());
		map.put("ten_day_du", ctdt.getTen_day_du());
		map.put("ten_bang", ctdt.getTen_bang());
		map.put("so_qd", ctdt.getSo_qd());
		map.put("ngay_qd", ctdt.getNgay_qd());
		map.put("id_nganh", ctdt.getId_nganh());
		map.put("so_nam_tk", ctdt.getSo_nam_tk());
		map.put("so_nam_td", ctdt.getSo_nam_td());
		return this.conn.Insert("CTDT", map);
	}

	/**
	 * @function update CTDT
	 * @param ctdt
	 * @return
	 * @throws Exception
	 */
	public boolean Update(CTDTM ctdt) throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("id_ctdt", ctdt.getId_ctdt());
		map.put("ten_van_tat", ctdt.getTen_van_tat());
		map.put("ten_day_du", ctdt.getTen_day_du());
		map.put("ten_bang", ctdt.getTen_bang());
		map.put("so_qd", ctdt.getSo_qd());
		map.put("ngay_qd", ctdt.getNgay_qd());
		map.put("id_nganh", ctdt.getId_nganh());
		map.put("so_nam_tk", ctdt.getSo_nam_tk());
		map.put("so_nam_td", ctdt.getSo_nam_td());
		return this.conn.Update("CTDT", map, "id_ctdt = " + ctdt.getId_ctdt());
	}

	/**
	 * @function delete CTDT
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public boolean Delete(int id) throws Exception {
		return this.conn.Delete("CTDT", "id_ctdt = " + id);
	}
}
